package com.sds.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import javax.servlet.http.HttpSession;

import com.sds.fw.dao.AbDao;
import com.sds.shop.vo.BoardVO;
import com.sds.shop.vo.MemVO;
import com.sds.shop.vo.SessionVO;

public class BoardDAO extends AbDao {
	private static BoardDAO dao = new BoardDAO();

	public static BoardDAO getInstance() {
		return dao;
	}

	public BoardVO getDetailBoard(String board_no) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		BoardVO vo = null;
		try {
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("select * from tb_board where board_no=?");

			ps = conn.prepareStatement(query.toString());
			ps.setString(1, board_no);

			rs = ps.executeQuery();

			while (rs.next()) {
				vo = new BoardVO();

				vo.setBOARD_NO(rs.getString(1));
				vo.setMEM_ID(rs.getString(2));
				vo.setTITLE(rs.getString(3));
				vo.setTEXT(rs.getString(4));
				vo.setCOUNT(Integer.parseInt(rs.getString(5)));
				vo.setDEL_FLG(rs.getString(6));
				vo.setREG_DTM(rs.getString(7));
				vo.setMOD_DTM(rs.getString(8));

			}

			if (vo == null) {
				return null;
			}

			close(conn, ps, rs);
		} catch (Exception ex) {
			System.out.println("getListBoard exception : " + ex);
			close(conn, ps, rs);
		}

		return vo;
	}
	
	public int getCountBoard() {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		try {
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("select count(board_no) from tb_board where lower(del_flg) like 'n'");

			ps = conn.prepareStatement(query.toString());
			

			rs = ps.executeQuery();

			while (rs.next()) {
				count = Integer.parseInt(rs.getString(1));
			}

			
			close(conn, ps, rs);
		} catch (Exception ex) {
			System.out.println("getCountBoard exception : " + ex);
			close(conn, ps, rs);
		}

		return count;
	}
	

	public Vector<BoardVO> getListBoard(String start, String end) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Vector<BoardVO> list = new Vector<BoardVO>();
		try {
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("select * from tb_board a, (select rank() over (order by to_number(board_no) desc) RANK, board_no ");
			query.append("from tb_board where lower(del_flg) like 'n') b ");
			query.append("where RANK between ? and ? and a.board_no = b.board_no ");

			ps = conn.prepareStatement(query.toString());
			ps.setString(1, start);
			ps.setString(2, end);

			rs = ps.executeQuery();
			while (rs.next()) {
				BoardVO vo = new BoardVO();

				vo.setBOARD_NO(rs.getString(1));
				vo.setMEM_ID(rs.getString(2));
				vo.setTITLE(rs.getString(3));
				vo.setTEXT(rs.getString(4));
				vo.setCOUNT(Integer.parseInt(rs.getString(5)));
				vo.setDEL_FLG(rs.getString(6));
				vo.setREG_DTM(rs.getString(7));
				vo.setMOD_DTM(rs.getString(8));

				list.add(vo);
			}
			close(conn, ps, rs);
		} catch (Exception ex) {
			System.out.println("getListBoard exception : " + ex);
			close(conn, ps, rs);
		}

		return list;
	}

	public boolean createBoard(String title, String text, String id) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Vector<BoardVO> list = new Vector<BoardVO>();
		try {
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("insert into tb_board values(((select nvl(max(to_number(board_no)), 0) from tb_board)+1), ");
			query.append("?, ?, ?, 0, 'N', to_char(sysdate, 'YYYYMMDDHH24MISS'), ");
			query.append("to_char(sysdate, 'YYYYMMDDHH24MISS'))");

			ps = conn.prepareStatement(query.toString());

			ps.setString(1, id);
			ps.setString(2, title);
			ps.setString(3, text);

			int index = ps.executeUpdate();

			if (index < 1) {
				return false;
			}
			close(conn, ps, rs);

		} catch (Exception ex) {
			System.out.println("createBoard exception : " + ex);
			close(conn, ps);
			return false;
		}

		return true;
	}

	public boolean updateCountBoard(String board_no) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("update tb_board set count=(count+1) where board_no=?");

			ps = conn.prepareStatement(query.toString());

			ps.setString(1, board_no);

			int index = ps.executeUpdate();

			if (index < 1) {
				return false;
			}
			close(conn, ps, rs);

		} catch (Exception ex) {
			System.out.println("updateCountBoard exception : " + ex);
			close(conn, ps);
			return false;
		}

		return true;
	}

	public boolean modifyboard(String board_no, String title, String text) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("update tb_board set title=?, text=?, mod_dtm=to_char(sysdate, 'YYYYMMDDHH24MISS') where board_no=?");

			ps = conn.prepareStatement(query.toString());

			ps.setString(1, title);
			ps.setString(2, text);
			ps.setString(3, board_no);

			int index = ps.executeUpdate();

			if (index < 1) {
				return false;
			}
			close(conn, ps, rs);

		} catch (Exception ex) {
			System.out.println("modifyboard exception : " + ex);
			close(conn, ps);
			return false;
		}

		return true;
	}

	public boolean deleteBoard(String board_no) {

		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("update tb_board set del_flg='Y' where board_no=?");

			ps = conn.prepareStatement(query.toString());

		
			ps.setString(1, board_no);

			int index = ps.executeUpdate();

			if (index < 1) {
				return false;
			}
			close(conn, ps, rs);

		} catch (Exception ex) {
			System.out.println("deleteBoard exception : " + ex);
			close(conn, ps);
			return false;
		}

			
		return true;
	}

}
